package com.codemonkey.lq.test.utils;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public final class SQLiteUtils {
	
	
	/**
	 * Sqlite DB文件所在的位置
	 */
	public final String DB_FILE_PATH;

	public final String CONNECT_URL;

	public final String CONNECT_USER;

	public final String CONNECT_PASSWORD;

	private final List<String> create_db_table_strings;

	static {
		try {
			Class.forName("org.sqlite.JDBC");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	private SQLiteUtils(Builder builder) {
		this.DB_FILE_PATH = builder.db_file_path;
		this.CONNECT_URL = "jdbc:sqlite:".concat(DB_FILE_PATH);
		this.CONNECT_USER = builder.user;
		this.CONNECT_PASSWORD = builder.password;

		this.create_db_table_strings = builder.strs_createTable;
	}

	/**
	 * 创建样例数据库
	 */
	public void createSampleDbAndTable() {
		Connection conn = null;
		Statement stat = null;

		String currentExecuteSqlString = null;
		try {
			conn = DriverManager.getConnection(CONNECT_URL, CONNECT_USER, CONNECT_PASSWORD);
			stat = conn.createStatement();

			for (String sqlString : create_db_table_strings) {
				currentExecuteSqlString = sqlString;
				stat.executeUpdate(sqlString);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("-------------------------------");
			System.out.println("current execute sql is : " + currentExecuteSqlString);
			System.out.println("-------------------------------");
		} finally {
			try {
				if (null != stat) {
					stat.close();
				}

				if (null != conn) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void destorySampleDb() {
		try {
			new File(DB_FILE_PATH).delete();
		} catch (Exception ignored) {
			ignored.printStackTrace();
			//ignore
		}
	}

	public static final class Builder {
		private final String db_file_path;

		/**
		 * 建表语句
		 */
		private List<String> strs_createTable = new ArrayList<String>() {
			/**
			 * 
			 */
			private static final long serialVersionUID = -8239469088110976714L;

			{
				// 默认创建person表
				// 其中有四个字段: 
				//    1. id
				//    2. name
				//    3. sex
				//    4. birthday
				add("DROP TABLE IF EXISTS person;");
				// 创建一个表，两列
				add("CREATE TABLE IF NOT EXISTS person(id int,name varchar(20), sex varchar(2), birthday date);");
				// 插入数据
				add("INSERT INTO person (id,name,sex,birthday) VALUES (1,'mike','m','1999-01-15')");
				add("INSERT INTO person (id,name,sex,birthday) VALUES (2,'lili','f','1997-08-12')");
				add("INSERT INTO person (id,name,sex,birthday) VALUES (3,'lucy','f','1996-02-03')");
				add("INSERT INTO person (id,name,sex,birthday) VALUES (3,'jojo','m','1998-10-22')");
			}
		};

		private String user = "admin";
		private String password = "admin";

		public Builder(String db_file_path) {
			super();
			this.db_file_path = db_file_path;
		}

		public Builder addCreateTableString(String createTableString) {
			strs_createTable.add(createTableString);
			return this;
		}

		public Builder user(String user) {
			this.user = user;
			return this;
		}

		public Builder password(String password) {
			this.password = password;
			return this;
		}

		public SQLiteUtils build() {
			return new SQLiteUtils(this);
		}

	}
}
